﻿using BLevy.Datos.Interfases;
using BLevy.Entidad;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using DapperExtensions;

namespace BLevy.Datos.Repositories
{
    public interface IProvinciaRepository : IRepository<Provincia>
    {
        IList<Provincia> FindAllActivos();
    }

    public class ProvinciaRepository : IProvinciaRepository
    {

        protected readonly IDbConnection _db;

        public ProvinciaRepository()
        {
            try
            {
                _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString());
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public IList<Provincia> FindAll()
        {
            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    string query = "SELECT * FROM PROVINCIAS ORDER BY ID ASC";

                    return _db.Query<Provincia>(query).ToList();
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public IQueryable<Provincia> Find(System.Linq.Expressions.Expression<Func<Provincia, bool>> predicate)
        {
            throw new NotImplementedException();
        }

        public Provincia FindById(int id)
        {
            string query = "SELECT * FROM PROVINCIAS " +
                "WHERE ID = " + id;

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<Provincia>(query).SingleOrDefault();
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public int Add(Provincia newEntity)
        {
            string query = "INSERT INTO PROVINCIAS (PROVINCIA, ACTIVO) VALUES (@provincia, @activo)";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Execute(query, new
                    {
                        provincia = newEntity.provincia,
                        activo = newEntity.activo
                    });
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public int Remove(int id)
        {
            string query = "UPDATE PROVINCIAS SET ACTIVO = 0 " +
                "WHERE ID = @id";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Execute(query, new { id = id });
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public int Modify(Provincia entity)
        {
            string query = "UPDATE PROVINCIAS SET PROVINCIA = @provincia, ACTIVO = @activo " +
                "WHERE ID = @id";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Execute(query, new { provincia = entity.provincia, activo = entity.activo, id = entity.id });
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public IList<Provincia> FindAllActivos()
        {
            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    string query = "SELECT * FROM PROVINCIAS " +
                        "WHERE ACTIVO = 1 ORDER BY ID ASC";

                    return _db.Query<Provincia>(query).ToList();
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }
    }
}
